package src.chapter35;

import java.sql.*;
import javax.sql.RowSet;

import com.sun.rowset.JdbcRowSetImpl;

public class ScrollUpdateRowSet {
    public static void main(String[] args)
            throws SQLException, ClassNotFoundException {
        // Load the JDBC driver
        Class.forName("com.mysql.jdbc.Driver");
        System.out.println("Driver loaded");

        // Create a row set
        RowSet rowSet = new JdbcRowSetImpl();

        // Set RowSet properties
        rowSet.setUrl("jdbc:mysql://localhost/javabook");
        rowSet.setUsername("scott");
        rowSet.setPassword("tiger");
        rowSet.setCommand("select state, capital from StateCapital");
        rowSet.execute();

        System.out.println("Before update ");
        displayRowSet(rowSet);

        // Update the second row
        rowSet.absolute(2); // Move cursor to the 2nd row
        rowSet.updateString("state", "New S"); // Update the column
        rowSet.updateString("capital", "New C"); // Update the column
        rowSet.updateRow(); // Update the row in the data source

        // Insert after the second row
        rowSet.last();
        rowSet.moveToInsertRow(); // Move cursor to the insert row
        rowSet.updateString("state", "Florida");
        rowSet.updateString("capital", "Tallahassee");
        rowSet.insertRow(); // Insert the row
        rowSet.moveToCurrentRow(); // Move the cursor to the current row

        // Delete fourth row
        rowSet.absolute(4); // Move cursor to the fifth row
        rowSet.deleteRow(); // Delete the second row

        System.out.println("After update ");
        displayRowSet(rowSet);

        // Close the connection
        rowSet.close();
    }

    private static void displayRowSet(RowSet rowSet)
            throws SQLException {
        ResultSetMetaData rsMetaData = rowSet.getMetaData();
        rowSet.beforeFirst();
        while (rowSet.next()) {
            for (int i = 1; i <= rsMetaData.getColumnCount(); i++)
                System.out.printf("%-12s\t", rowSet.getObject(i));
            System.out.println();
        }
    }
}
